﻿using System.Collections.Generic;
using System.Data;
using System.Linq;

using SWE4.Data.EmbeddedSensorCloud.DTO.Request;
using SWE4.Data.EmbeddedSensorCloud.DTO.Response;
using MySql.Data.MySqlClient;

namespace EmbeddedSensorCloud.Plugin.MicroERP
{
    public class MySQLCustomerDAO : MySQLDAOBase, ICustomerDAO
    {
        #region Constructor

        public MySQLCustomerDAO(string connectionString)
            : base(connectionString)
        { }

        #endregion

        #region ICustomerDAO

        public uint Create(CustomerRequestModel customer)
        {
            using (MySqlConnection connection = base.GetConnection())
            using (MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    string contactInsertSQL = "INSERT INTO `contacts` (`address`, `deliveryAddress`, `billingAddress`) VALUES (@address, @deliveryAddress, @billingAddress);";

                    MySqlCommand contactInsertCommand = new MySqlCommand(contactInsertSQL, connection, transaction);
                    contactInsertCommand.Prepare();
                    contactInsertCommand.Parameters.AddWithValue("@address", customer.Address);
                    contactInsertCommand.Parameters.AddWithValue("@deliveryAddress", customer.DeliveryAddress);
                    contactInsertCommand.Parameters.AddWithValue("@billingAddress", customer.BillingAddress);
                    contactInsertCommand.ExecuteNonQuery();

                    uint lastId = base.GetLastInsertId(connection, transaction);

                    string specificInsertSQL;
                    if (customer is PersonRequestModel)
                    {
                        specificInsertSQL = "INSERT INTO `persons` (`id`, `title`, `firstname`, `lastname`, `suffix`, `birthdate`, `companyId_fk`) VALUES (@id, @title, @firstname, @lastname, @suffix, @birthdate, @companyId_fk);";
                    }
                    else
                    {
                        specificInsertSQL = "INSERT INTO `companies` (`id`, `name`, `uid`) VALUES (@id, @name, @uid);";
                    }

                    MySqlCommand specificInsertCommand = new MySqlCommand(specificInsertSQL, connection, transaction);
                    specificInsertCommand.Prepare();

                    if (customer is PersonRequestModel)
                    {
                        var person = (PersonRequestModel)customer;
                        specificInsertCommand.Parameters.AddWithValue("@id", lastId);
                        specificInsertCommand.Parameters.AddWithValue("@title", person.Title);
                        specificInsertCommand.Parameters.AddWithValue("@firstname", person.FirstName);
                        specificInsertCommand.Parameters.AddWithValue("@lastname", person.LastName);
                        specificInsertCommand.Parameters.AddWithValue("@suffix", person.Suffix);
                        specificInsertCommand.Parameters.AddWithValue("@birthdate", person.BirthDate);
                        specificInsertCommand.Parameters.AddWithValue("@companyId_fk", person.CompanyId);
                    }
                    else
                    {
                        var company = (CompanyRequestModel)customer;
                        specificInsertCommand.Parameters.AddWithValue("@id", lastId);
                        specificInsertCommand.Parameters.AddWithValue("@name", company.Name);
                        specificInsertCommand.Parameters.AddWithValue("@uid", company.UId);
                    }
                    specificInsertCommand.ExecuteNonQuery();

                    transaction.Commit();
                    return lastId;
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public IEnumerable<CustomerResponseModel> Read(string searchQuery, bool onlyCompanies)
        {
            using (MySqlConnection connection = base.GetConnection())
            using (MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
            {
                try
                {
                    string companySearchSQL = "SELECT c.`id`, co.`name` FROM `contacts` c LEFT JOIN `companies` co ON c.`id` = co.`id` WHERE co.`name` LIKE @companyName;";
                    string personSearchSQL = "SELECT c.`id`, p.`firstname`, p.`lastname` FROM `contacts` c LEFT JOIN `persons` p ON c.`id` = p.`id` WHERE p.`firstname` LIKE @personName OR p.`lastname` LIKE @personName;";

                    var companyList = new List<CompanyResponseModel>();
                    var personList = new List<PersonResponseModel>();

                    MySqlCommand companySearchCommand = new MySqlCommand(companySearchSQL, connection, transaction);
                    companySearchCommand.Prepare();
                    companySearchCommand.Parameters.AddWithValue("@companyName", string.Format("%{0}%", searchQuery));

                    using (MySqlDataReader reader = companySearchCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            companyList.Add(new CompanyResponseModel()
                            {
                                Id = reader.GetUInt32("id"),
                                Name = reader.GetString("name")
                            });
                        }
                    }

                    if (!onlyCompanies)
                    {
                        MySqlCommand personSearchCommand = new MySqlCommand(personSearchSQL, connection, transaction);
                        personSearchCommand.Prepare();
                        personSearchCommand.Parameters.AddWithValue("@personName", string.Format("%{0}%", searchQuery));

                        using (MySqlDataReader reader = personSearchCommand.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                personList.Add(new PersonResponseModel()
                                {
                                    Id = reader.GetUInt32("id"),
                                    FirstName = reader.GetString("firstname"),
                                    LastName = reader.GetString("lastname")
                                });
                            }
                        }
                    }
                    transaction.Rollback();

                    return companyList.Concat<CustomerResponseModel>(personList);
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public CustomerResponseModel Read(uint customerID)
        {
            using (MySqlConnection connection = base.GetConnection())
            using (MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
            {
                try
                {
                    string contactSQL = "SELECT c.`id`, c.`address`, c.`deliveryAddress`, c.`billingAddress`, co.`name`, co.`uid`, p.`title`, p.`firstname`, p.`lastname`, p.`suffix`, p.`birthdate`, p.`companyId_fk` FROM `contacts` c LEFT JOIN `companies` co ON c.`id` = co.`id` LEFT JOIN `persons` p ON c.`id` = p.`id` WHERE c.`id` = @contactId;";
                    CustomerResponseModel customer = null;

                    MySqlCommand contactsCommand = new MySqlCommand(contactSQL, connection, transaction);
                    contactsCommand.Prepare();
                    contactsCommand.Parameters.AddWithValue("@contactId", customerID);
                    uint personCompanyId = 0;
                    bool personHasCompany = false;

                    using (MySqlDataReader reader = contactsCommand.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.Read())
                        {
                            if (!reader.IsDBNull(reader.GetOrdinal("firstname")))
                            {
                                customer = new PersonResponseModel()
                                {
                                    FirstName = reader.GetString("firstname"),
                                    LastName = reader.GetString("lastname")
                                };
                                if (!reader.IsDBNull(reader.GetOrdinal("billingAddress"))) { customer.BillingAddress = reader.GetString("billingAddress"); }
                                if (!reader.IsDBNull(reader.GetOrdinal("deliveryAddress"))) { customer.DeliveryAddress = reader.GetString("deliveryAddress"); }
                                if (!reader.IsDBNull(reader.GetOrdinal("title"))) { (customer as PersonResponseModel).Title = reader.GetString("title"); }
                                if (!reader.IsDBNull(reader.GetOrdinal("suffix"))) { (customer as PersonResponseModel).Suffix = reader.GetString("suffix"); }
                                if (!reader.IsDBNull(reader.GetOrdinal("birthdate"))) { (customer as PersonResponseModel).BirthDate = reader.GetDateTime("birthdate"); }

                                if (!reader.IsDBNull(reader.GetOrdinal("companyId_fk")))
                                {
                                    personCompanyId = reader.GetUInt32("companyId_fk");
                                    personHasCompany = true;
                                }
                            }
                            else
                            {
                                customer = new CompanyResponseModel()
                                {
                                    Name = reader.GetString("name"),
                                    UId = reader.GetString("uid")
                                };
                            }

                            customer.Id = reader.GetUInt32("id");
                            customer.Address = reader.GetString("address");
                            if (!reader.IsDBNull(reader.GetOrdinal("billingAddress"))) { customer.BillingAddress = reader.GetString("billingAddress"); }
                            if (!reader.IsDBNull(reader.GetOrdinal("deliveryAddress"))) { customer.DeliveryAddress = reader.GetString("deliveryAddress"); }
                        }
                    }
                    if (personHasCompany)
                    {
                        (customer as PersonResponseModel).Company = GetCompanyForPerson(connection, transaction, personCompanyId);
                    }

                    transaction.Rollback();
                    return customer;
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public void Update(CustomerRequestModel customer, uint customerId)
        {
            using (MySqlConnection connection = base.GetConnection())
            using (MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    string contactUpdateSQL = "UPDATE `contacts` SET `address` = @address, `deliveryAddress` = @deliveryAddress, `billingAddress` = @billingAddress WHERE `id` = @id;";

                    MySqlCommand contactUpdateCommand = new MySqlCommand(contactUpdateSQL, connection, transaction);
                    contactUpdateCommand.Prepare();
                    contactUpdateCommand.Parameters.AddWithValue("@id", customerId);
                    contactUpdateCommand.Parameters.AddWithValue("@address", customer.Address);
                    contactUpdateCommand.Parameters.AddWithValue("@deliveryAddress", customer.DeliveryAddress);
                    contactUpdateCommand.Parameters.AddWithValue("@billingAddress", customer.BillingAddress);
                    contactUpdateCommand.ExecuteNonQuery();

                    string specificUpdateSQL;
                    if (customer is PersonRequestModel)
                    {
                        specificUpdateSQL = "UPDATE `persons` SET `title` = @title, `firstname` = @firstname, `lastname` = @lastname, `suffix` = @suffix, `birthdate` = @birthdate, `companyId_fk` = @companyId_fk WHERE `id` = @id;";
                    }
                    else
                    {
                        specificUpdateSQL = "UPDATE `companies` SET `name` = @name, `uid` = @uid WHERE `id` = @id;";
                    }

                    MySqlCommand specificUpdateCommand = new MySqlCommand(specificUpdateSQL, connection, transaction);
                    specificUpdateCommand.Prepare();

                    if (customer is PersonRequestModel)
                    {
                        var person = (PersonRequestModel)customer;
                        specificUpdateCommand.Parameters.AddWithValue("@id", customerId);
                        specificUpdateCommand.Parameters.AddWithValue("@title", person.Title);
                        specificUpdateCommand.Parameters.AddWithValue("@firstname", person.FirstName);
                        specificUpdateCommand.Parameters.AddWithValue("@lastname", person.LastName);
                        specificUpdateCommand.Parameters.AddWithValue("@suffix", person.Suffix);
                        specificUpdateCommand.Parameters.AddWithValue("@birthdate", person.BirthDate);
                        specificUpdateCommand.Parameters.AddWithValue("@companyId_fk", person.CompanyId);
                    }
                    else
                    {
                        var company = (CompanyRequestModel)customer;
                        specificUpdateCommand.Parameters.AddWithValue("@id", customerId);
                        specificUpdateCommand.Parameters.AddWithValue("@name", company.Name);
                        specificUpdateCommand.Parameters.AddWithValue("@uid", company.UId);
                    }
                    specificUpdateCommand.ExecuteNonQuery();

                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public void Delete(uint customerID)
        {
            using (MySqlConnection connection = base.GetConnection())
            using (MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    string checkForInvoicesSQL = "SELECT * FROM `invoices` i WHERE i.`contactId_fk` = @id;";
                    string customerDeleteSQL = "DELETE FROM `contacts` WHERE `id` = @id;";

                    MySqlCommand checkForInvoicesCommand = new MySqlCommand(checkForInvoicesSQL, connection, transaction);
                    checkForInvoicesCommand.Prepare();
                    checkForInvoicesCommand.Parameters.AddWithValue("@id", customerID);

                    using (MySqlDataReader reader = checkForInvoicesCommand.ExecuteReader())
                    {
                        if(reader.Read())
                        {
                            throw new DeleteCustomerWithInvoicesException();
                        }
                    }

                    MySqlCommand customerDeleteCommand = new MySqlCommand(customerDeleteSQL, connection, transaction);
                    customerDeleteCommand.Prepare();
                    customerDeleteCommand.Parameters.AddWithValue("@id", customerID);
                    customerDeleteCommand.ExecuteNonQuery();

                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        #endregion

        /// <summary>
        /// Retrieves a company model for a given id.
        /// </summary>
        /// <exception cref="CompanyNotFoundException" />
        private CompanyResponseModel GetCompanyForPerson(MySqlConnection connection, MySqlTransaction transaction, uint companyId)
        {
            string personCompanySQL = "SELECT c.`id`, co.`name` FROM `companies` co INNER JOIN `contacts` c on c.`id` = co.`id` WHERE co.`id` = @companyId;";

            MySqlCommand personCompanyCommand = new MySqlCommand(personCompanySQL, connection, transaction);
            personCompanyCommand.Prepare();
            personCompanyCommand.Parameters.AddWithValue("@companyId", companyId);

            using (MySqlDataReader reader = personCompanyCommand.ExecuteReader(CommandBehavior.SingleRow))
            {
                if (reader.Read())
                {
                    return new CompanyResponseModel()
                    {
                        Id = reader.GetUInt32("id"),
                        Name = reader.GetString("name")
                    };
                }
                throw new CompanyNotFoundException();
            }
        }
    }
}
